create external table jms_dm.dm_reback_timely_rate_sum (
   date_time string  comment '日期' ,
   actual_network_code string comment '实际操作网点code' ,
   actual_network_name string comment '实际操作网点name' ,
   total_num bigint comment '总件量',
   total_money decimal(16,4) comment '总件量金额',
   total_is_timely_true bigint comment '总及时件量',
   total_is_timely_false bigint comment '总不及时量',
   is_timely_true bigint comment '类型分类及时',
   is_timely_false bigint comment '类型分类不及时',
   waybill_num bigint comment '件量',
   waybill_money decimal(16,4) comment '罚款金额',
   duty_cal_1 bigint comment '1未打印面单 ',
   duty_cal_2 bigint comment '2已打印未发出',
   duty_cal_3 bigint comment '3已打印漏扫发件',
   duty_cal_4 bigint comment '4非应操作网点发出',
   duty_cal_5 bigint comment '5下一站晚到',
   duty_cal_6 bigint comment '6虚假发件',
   type_flg string COMMENT '1退转件，2拦截件'
) COMMENT '退件及时率汇总表'
PARTITIONED BY (dt STRING COMMENT '审核时间')
STORED AS PARQUET
LOCATION '/dw/hive/jms_dm.db/external/dm_reback_timely_rate_sum'
TBLPROPERTIES (
'discover.partitions'='false',
'parquet.column.index.access'='true'
);





create  table jms_dm.dm_reback_timely_rate_sum (
   date_time date  comment '日期' ,
   actual_network_code varchar(200) comment '实际操作网点code' ,
   actual_network_name varchar(200) comment '实际操作网点name' ,
   total_num bigint comment '总件量',
   total_money decimal(16,4) comment '总件量金额',
   total_is_timely_true bigint comment '总及时件量',
   total_is_timely_false bigint comment '总不及时量',
   is_timely_true bigint comment '类型分类及时',
   is_timely_false bigint comment '类型分类不及时',
   waybill_num bigint comment '件量',
   waybill_money decimal(16,4) comment '罚款金额',
   duty_cal_1 bigint comment '1未打印面单 ',
   duty_cal_2 bigint comment '2已打印未发出',
   duty_cal_3 bigint comment '3已打印漏扫发件',
   duty_cal_4 bigint comment '4非应操作网点发出',
   duty_cal_5 bigint comment '5下一站晚到',
   duty_cal_6 bigint comment '6虚假发件',
   type_flg varchar(200) COMMENT '1退转件，2拦截件'
)
ENGINE=OLAP
DUPLICATE KEY(date_time,actual_network_code)
comment '退件及时率汇总表'
PARTITION BY RANGE (date_time) (
   START ("2023-07-01") END ("2023-07-31") EVERY (INTERVAL 1 day))
DISTRIBUTED BY HASH(actual_network_code,type_flg) BUCKETS 2
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-1000",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "2",
"in_memory" = "false",
"storage_format" = "V2"
);